﻿/******************************************************************************
'Description:装试考核看板类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Zhunan
'create Date:2019.6.21
 ********************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace ReportBusiness
{
    public class ZSKaoHeLookBoardBusiness
    {
        private uMESReportOracleDAL dal = new uMESReportOracleDAL();

        //本月任务机型分布(饼图) add zhunan 2019.10.24
        public DataTable GetProdutfamily()
        {
            string sql = @"
                            SELECT pf.productfamilyname,COUNT(t.mfgorderid) qty
                            FROM
                            (
                              SELECT ua.attributevalue,mfg.mfgorderid
                              FROM enginepartplan ep
                              LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
                              LEFT JOIN Userattribute ua ON ua.parentid = ep.mfgorderid AND ua.userattributename='发动机'
                              LEFT JOIN workflowstep ws ON ws.workflowstepid = ep.workflowstepid
                              WHERE ep.type = 1 AND ep.workflowstepid IS NOT NULL AND ws.islaststep = 1 AND ep.usecompleted = 0  AND ep.completestepid IS NULL
                              GROUP BY ua.attributevalue,mfg.mfgorderid

                              UNION ALL 

                              SELECT ua.attributevalue,mfg.mfgorderid
                              FROM enginepartplan ep
                              LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
                              LEFT JOIN Userattribute ua ON ua.parentid = ep.mfgorderid AND ua.userattributename='发动机'
                              WHERE ep.type = 1 AND ep.completestepid IS NOT NULL AND ep.workflowstepid IS NOT NULL AND ep.completestepid = ep.workflowstepid AND ep.usecompleted = 0
                              GROUP BY ua.attributevalue,mfg.mfgorderid

                              UNION ALL 
  
                              SELECT ua.attributevalue,mfg.mfgorderid
                              FROM enginepartplan ep
                              LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
                              LEFT JOIN Userattribute ua ON ua.parentid = ep.mfgorderid AND ua.userattributename='发动机'
                              WHERE ep.type <> 1
                              HAVING COUNT(1) <> SUM(ep.usecompleted)
                              GROUP BY ua.attributevalue,mfg.mfgorderid
                            )
                            t 
                            LEFT JOIN mfgorder mfg ON mfg.mfgorderid = t.mfgorderid
                            LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid--查机型
                            LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
                            LEFT JOIN Workflowproductfamilylist wpf ON wpf.workflowid = p.workflowid
                            LEFT JOIN productfamily pf ON pf.productfamilyid = wpf.productfamilylistid
                            WHERE pf.productfamilyname IS NOT NULL 
                            GROUP BY pf.productfamilyname";

            return dal.GetDataTable(sql);
        }

        //获取各工区剩余任务 add zhunan 2019.12.11
        public DataTable GetWorkcenterNotFinishedInfo(Dictionary<string, object> para)
        {
            string sql = @"
SELECT w.workcentername
FROM workcenter w
WHERE w.wctype = 1
";
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format("  AND w.factoryid = '{0}' ", para["factoryid"].ToString());
            }

            sql += "ORDER BY w.workcentername";

            DataTable dtResult = dal.GetDataTable(sql);
            dtResult.Columns.Add("notFinish");//未完成

            #region 查询各工区剩余任务
            sql = @"
SELECT m.mfgorderid,m.workcentername
FROM
(
    --任务单所派工工区
    SELECT t.mfgorderid,w.workcentername
    FROM
    (
        SELECT te.mfgorderid
        FROM
        (
          --未定义完工序，且最后一道大阶段工序未完成
          SELECT mfg.mfgorderid,u.attributevalue
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE ep.type = 1 AND ep.productid = p.productid AND ep.completestepid IS NULL AND ws.islaststep = 1 AND ep.usecompleted = 0
          GROUP BY mfg.mfgorderid,u.attributevalue
          --已定义完工序，且完工序未完成
          UNION ALL
          SELECT mfg.mfgorderid,u.attributevalue
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE ep.type = 1 AND ep.productid = p.productid AND ep.completestepid IS NOT NULL AND ep.workflowstepid = ep.completestepid AND ep.usecompleted = 0
          GROUP BY mfg.mfgorderid,u.attributevalue
          --施工单，且未全部完成
          UNION ALL 
          SELECT mfg.mfgorderid,u.attributevalue
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE ep.type = 2 AND ep.productid = p.productid
          HAVING SUM(ep.usecompleted) <> COUNT(1)
          GROUP BY mfg.mfgorderid,u.attributevalue
        ) te
        GROUP BY te.mfgorderid
    ) t
    LEFT JOIN enginepartplan ep ON ep.mfgorderid = t.mfgorderid
    LEFT JOIN Workcenter w ON w.workcenterid = ep.workcenterid
    WHERE ep.containerno IS NOT NULL AND ep.type = 1 AND w.workcentername IS NOT NULL
    GROUP BY t.mfgorderid,w.workcentername
    --施工单所派工区
    UNION ALL
    SELECT t.mfgorderid,w.workcentername
    FROM
    (
        SELECT te.mfgorderid
        FROM
        (
          --未定义完工序，且最后一道大阶段未完成
          SELECT mfg.mfgorderid,u.attributevalue
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE ep.type = 1 AND ep.productid = p.productid AND ep.completestepid IS NULL AND ws.islaststep = 1 AND ep.usecompleted = 0
          GROUP BY mfg.mfgorderid,u.attributevalue
          --已定义完工序，且完工序未完成
          UNION ALL
          SELECT mfg.mfgorderid,u.attributevalue
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE ep.type = 1 AND ep.productid = p.productid AND ep.completestepid IS NOT NULL AND ep.workflowstepid = ep.completestepid AND ep.usecompleted = 0
          GROUP BY mfg.mfgorderid,u.attributevalue
          --施工单，且未全部完成
          UNION ALL 
          SELECT mfg.mfgorderid,u.attributevalue
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE ep.type = 2 AND ep.productid = p.productid
          HAVING SUM(ep.usecompleted) <> COUNT(1)
          GROUP BY mfg.mfgorderid,u.attributevalue
        ) te
        GROUP BY te.mfgorderid
    ) t
    LEFT JOIN enginepartplan ep ON ep.mfgorderid = t.mfgorderid
    LEFT JOIN containerteamdispatch csd ON csd.containerid = nvl(ep.containerid,ep.enginecontainerid)
    LEFT JOIN team te ON te.teamid = csd.teamid
    LEFT JOIN shift s ON s.shiftid=te.shiftid
    LEFT JOIN Workcenter w ON w.workcenterid = s.workcenterid
    WHERE ep.type = 2 AND w.workcentername IS NOT NULL
    GROUP BY t.mfgorderid,w.workcentername
) m
GROUP BY m.mfgorderid,m.workcentername
";
            DataTable dtNotFinish = dal.GetDataTable(sql);
            #endregion

            foreach (DataRow dr in dtResult.Rows)
            {
                string filter = string.Format(" workcentername = '{0}'", dr["workcentername"]);
                DataRow[] drNotFinish = dtNotFinish.Select(filter);
                dr["notFinish"] = drNotFinish.Length > 0 ? drNotFinish.Length : 0;
            }

            return dtResult;
        }

        //获取各工区超期任务 add zhunan 2019.12.11
        public DataTable GetWorkcenterDelayInfo(Dictionary<string, object> para)
        {
            string sql = @"
SELECT w.workcentername
FROM workcenter w
WHERE w.wctype = 1
";
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format("  AND w.factoryid = '{0}' ", para["factoryid"].ToString());
            }

            DataTable dtResult = dal.GetDataTable(sql);
            dtResult.Columns.Add("delay");//未完成

            #region 查询已超期订单
            sql = @"
SELECT m.mfgorderid,m.workcentername
FROM
(
    --任务单所派工工区
    SELECT t.mfgorderid,w.workcentername
    FROM
    (
        SELECT te.mfgorderid
        FROM
        (
          --未定义完工序，且最后一道大阶段工序未完成
          SELECT mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate) delaydays
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE mfg.factorycompletiondate IS NOT NULL AND  ep.type = 1 AND ep.productid = p.productid 
                AND ep.completestepid IS NULL AND ws.islaststep = 1 AND ep.usecompleted = 0
          GROUP BY mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate)
          --已定义完工序，且完工序未完成
          UNION ALL
          SELECT mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate) delaydays
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE mfg.factorycompletiondate IS NOT NULL AND ep.type = 1 AND ep.productid = p.productid 
                AND ep.completestepid IS NOT NULL AND ep.workflowstepid = ep.completestepid AND ep.usecompleted = 0
          GROUP BY mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate)
          --施工单，且未全部完成
          UNION ALL 
          SELECT mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate) delaydays
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE mfg.factorycompletiondate IS NOT NULL AND ep.type = 2 AND ep.productid = p.productid
          HAVING SUM(ep.usecompleted) <> COUNT(1)
          GROUP BY mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate)
        ) te
        WHERE te.delaydays > 0
        GROUP BY te.mfgorderid
    ) t
    LEFT JOIN enginepartplan ep ON ep.mfgorderid = t.mfgorderid
    LEFT JOIN Workcenter w ON w.workcenterid = ep.workcenterid
    WHERE ep.containerno IS NOT NULL AND ep.type = 1 AND w.workcentername IS NOT NULL
    GROUP BY t.mfgorderid,w.workcentername
    --施工单所派工区
    UNION ALL
    SELECT t.mfgorderid,w.workcentername
    FROM
    (
        SELECT te.mfgorderid
        FROM
        (
          --未定义完工序，且最后一道大阶段未完成
          SELECT mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate) delaydays
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE mfg.factorycompletiondate IS NOT NULL AND ep.type = 1 AND ep.productid = p.productid 
                AND ep.completestepid IS NULL AND ws.islaststep = 1 AND ep.usecompleted = 0
          GROUP BY mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate)
          --已定义完工序，且完工序未完成
          UNION ALL
          SELECT mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate) delaydays
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE mfg.factorycompletiondate IS NOT NULL AND ep.type = 1 AND ep.productid = p.productid 
                AND ep.completestepid IS NOT NULL AND ep.workflowstepid = ep.completestepid AND ep.usecompleted = 0
          GROUP BY mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate)
          --施工单，且未全部完成
          UNION ALL 
          SELECT mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate) delaydays
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE mfg.factorycompletiondate IS NOT NULL AND ep.type = 2 AND ep.productid = p.productid
          HAVING SUM(ep.usecompleted) <> COUNT(1)
          GROUP BY mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate)
        ) te
        WHERE te.delaydays > 0
        GROUP BY te.mfgorderid
    ) t
    LEFT JOIN enginepartplan ep ON ep.mfgorderid = t.mfgorderid
    LEFT JOIN containerteamdispatch csd ON csd.containerid = nvl(ep.containerid,ep.enginecontainerid)
    LEFT JOIN team te ON te.teamid = csd.teamid
    LEFT JOIN shift s ON s.shiftid=te.shiftid
    LEFT JOIN Workcenter w ON w.workcenterid = s.workcenterid
    WHERE ep.type = 2 AND w.workcentername IS NOT NULL
    GROUP BY t.mfgorderid,w.workcentername
) m
GROUP BY m.mfgorderid,m.workcentername
";
            DataTable dtDelay = dal.GetDataTable(sql);
            #endregion

            foreach (DataRow dr in dtResult.Rows)
            {
                string filter = string.Format(" workcentername = '{0}'", dr["workcentername"]);
                DataRow[] drDelay = dtDelay.Select(filter);
                dr["delay"] = drDelay.Length > 0 ? drDelay.Length : 0;
            }
            return dtResult;
        }

        //各中心任务(柱状图)
        public DataTable GetWorkcenterBarInfo(Dictionary<string, object> para)
        {
            string sql = @"
SELECT w.workcentername
FROM workcenter w
WHERE w.wctype = 1
";
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format("  AND w.factoryid = '{0}' ", para["factoryid"].ToString());
            }

            sql += " ORDER BY w.workcentername";

            DataTable dtResult = dal.GetDataTable(sql);
            dtResult.Columns.Add("finishQty");//已完成
            dtResult.Columns.Add("delayQty");//超期
            dtResult.Columns.Add("notFinishQty");//剩余

            #region 查询各工区已完成订单
            sql = @"
SELECT m.mfgorderid,m.workcentername
FROM
(
    --任务单所派工工区
    SELECT t.mfgorderid,w.workcentername
    FROM
    (
        SELECT te.mfgorderid
        FROM
        (
          --未定义完工序，且最后一道大阶段工序在本月完成
          SELECT mfg.mfgorderid,u.attributevalue,MAX(ep.finishdate) finishdate
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE ep.type = 1 AND ep.productid = p.productid AND ep.completestepid IS NULL AND ws.islaststep = 1 AND ep.usecompleted = 1
                AND ep.finishdate >= (trunc(add_months(sysdate,-1),'mm') + 20) AND ep.finishdate <= (trunc(sysdate,'mm') + 19)
          GROUP BY mfg.mfgorderid,u.attributevalue
          --已定义完工序，且完工工序在本月完成
          UNION ALL
          SELECT mfg.mfgorderid,u.attributevalue,MAX(ep.finishdate) finishdate
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE ep.type = 1 AND ep.productid = p.productid AND ep.completestepid IS NOT NULL AND ep.workflowstepid = ep.completestepid AND ep.usecompleted = 1
                AND ep.finishdate >= (trunc(add_months(sysdate,-1),'mm') + 20) AND ep.finishdate <= (trunc(sysdate,'mm') + 19)
          GROUP BY mfg.mfgorderid,u.attributevalue
          --施工单，在本月内全部完工
          UNION ALL 
          SELECT mfg.mfgorderid,u.attributevalue,MAX(ep.finishdate) finishdate
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE ep.type = 2 AND ep.productid = p.productid
          HAVING SUM(ep.usecompleted) = COUNT(1) AND MAX(ep.finishdate) >= (trunc(add_months(sysdate,-1),'mm') + 20) AND MAX(ep.finishdate) <= (trunc(sysdate,'mm') + 19)
          GROUP BY mfg.mfgorderid,u.attributevalue
        ) te
        GROUP BY te.mfgorderid
    ) t
    LEFT JOIN enginepartplan ep ON ep.mfgorderid = t.mfgorderid
    LEFT JOIN Workcenter w ON w.workcenterid = ep.workcenterid
    WHERE ep.containerno IS NOT NULL AND ep.type = 1 AND w.workcentername IS NOT NULL
    GROUP BY t.mfgorderid,w.workcentername
    --施工单所派工区
    UNION ALL
    SELECT t.mfgorderid,w.workcentername
    FROM
    (
        SELECT te.mfgorderid
        FROM
        (
          --未定义完工序，且最后一道大阶段工序在本月完成
          SELECT mfg.mfgorderid,u.attributevalue,MAX(ep.finishdate) finishdate
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE ep.type = 1 AND ep.productid = p.productid AND ep.completestepid IS NULL AND ws.islaststep = 1 AND ep.usecompleted = 1
                AND ep.finishdate >= (trunc(add_months(sysdate,-1),'mm') + 20) AND ep.finishdate <= (trunc(sysdate,'mm') + 19)
          GROUP BY mfg.mfgorderid,u.attributevalue
          --已定义完工序，且完工工序在本月完成
          UNION ALL
          SELECT mfg.mfgorderid,u.attributevalue,MAX(ep.finishdate) finishdate
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE ep.type = 1 AND ep.productid = p.productid AND ep.completestepid IS NOT NULL AND ep.workflowstepid = ep.completestepid AND ep.usecompleted = 1
                AND ep.finishdate >= (trunc(add_months(sysdate,-1),'mm') + 20) AND ep.finishdate <= (trunc(sysdate,'mm') + 19)
          GROUP BY mfg.mfgorderid,u.attributevalue
          --施工单，在本月内全部完工
          UNION ALL 
          SELECT mfg.mfgorderid,u.attributevalue,MAX(ep.finishdate) finishdate
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE ep.type = 2 AND ep.productid = p.productid
          HAVING SUM(ep.usecompleted) = COUNT(1) AND MAX(ep.finishdate) >= (trunc(add_months(sysdate,-1),'mm') + 20) AND MAX(ep.finishdate) <= (trunc(sysdate,'mm') + 19)
          GROUP BY mfg.mfgorderid,u.attributevalue
        ) te
        GROUP BY te.mfgorderid
    ) t
    LEFT JOIN enginepartplan ep ON ep.mfgorderid = t.mfgorderid
    LEFT JOIN containerteamdispatch csd ON csd.containerid = nvl(ep.containerid,ep.enginecontainerid)
    LEFT JOIN team te ON te.teamid = csd.teamid
    LEFT JOIN shift s ON s.shiftid=te.shiftid
    LEFT JOIN Workcenter w ON w.workcenterid = s.workcenterid
    WHERE ep.type = 2 AND w.workcentername IS NOT NULL
    GROUP BY t.mfgorderid,w.workcentername
) m
GROUP BY m.mfgorderid,m.workcentername
";
            DataTable dtFinish = dal.GetDataTable(sql);
            #endregion

            //查询剩余
            DataTable dtNotFinish = GetWorkcenterNotFinishedInfo(para);

            //查询超期
            DataTable dtDelay = GetWorkcenterDelayInfo(para);

            foreach (DataRow dr in dtResult.Rows)
            {
                string filter = string.Format(" workcentername = '{0}'", dr["workcentername"]);
                DataRow[] drFinish = dtFinish.Select(filter);
                dr["finishQty"] = drFinish.Length > 0 ? drFinish.Length : 0;
                dr["notFinishQty"] = dtNotFinish.Select(filter).Length > 0 ? dtNotFinish.Select(filter)[0]["notFinish"] : 0;
                dr["delayQty"] = dtDelay.Select(filter).Length > 0 ? dtDelay.Select(filter)[0]["delay"] : 0;
            }

            return dtResult;
        }

        //各中心已完成明细 add zhunan 2019.12.12
        public DataTable GetWorkcenterFinishDetailInfo(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            string sql = @"
SELECT m.mfgorderid,m.workcentername,u.attributevalue,mfg.factorycompletiondate,NVL(n.qty,0) qty,m.finishdate,
       CASE WHEN mfg.factorycompletiondate IS NULL THEN '计划完成时间为空,不能计算'
            WHEN TRUNC(m.finishdate - mfg.factorycompletiondate) > 0
                 THEN to_char(TRUNC(m.finishdate - mfg.factorycompletiondate))
            WHEN TRUNC(m.finishdate - mfg.factorycompletiondate) <= 0 THEN '0'
       END delayDays,
       CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
             ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                        WHEN ot.ordertypename = 'PD02' THEN '新机'
                        WHEN ot.ordertypename = 'PD03' THEN '新机'
                        WHEN ot.ordertypename = 'PD05' THEN '大修'
                        WHEN ot.ordertypename = 'PD06' THEN '检修'
                   ELSE ot.ordertypename END
                  ) 
       END ordertypename
FROM
(
    --任务单所派工工区
    SELECT t.mfgorderid,w.workcentername,t.finishdate
    FROM
    (
        SELECT te.mfgorderid,te.finishdate
        FROM
        (
          --未定义完工序，且最后一道大阶段工序在本月完成
          SELECT mfg.mfgorderid,u.attributevalue,MAX(ep.finishdate) finishdate
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE ep.type = 1 AND ep.productid = p.productid AND ep.completestepid IS NULL AND ws.islaststep = 1 AND ep.usecompleted = 1
                AND ep.finishdate >= (trunc(add_months(sysdate,-1),'mm') + 20) AND ep.finishdate <= (trunc(sysdate,'mm') + 19)
          GROUP BY mfg.mfgorderid,u.attributevalue
          --已定义完工序，且完工工序在本月完成
          UNION ALL
          SELECT mfg.mfgorderid,u.attributevalue,MAX(ep.finishdate) finishdate
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE ep.type = 1 AND ep.productid = p.productid AND ep.completestepid IS NOT NULL AND ep.workflowstepid = ep.completestepid AND ep.usecompleted = 1
                AND ep.finishdate >= (trunc(add_months(sysdate,-1),'mm') + 20) AND ep.finishdate <= (trunc(sysdate,'mm') + 19)
          GROUP BY mfg.mfgorderid,u.attributevalue
          --施工单，在本月内全部完工
          UNION ALL 
          SELECT mfg.mfgorderid,u.attributevalue,MAX(ep.finishdate) finishdate
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE ep.type = 2 AND ep.productid = p.productid
          HAVING SUM(ep.usecompleted) = COUNT(1) AND MAX(ep.finishdate) >= (trunc(add_months(sysdate,-1),'mm') + 20) AND MAX(ep.finishdate) <= (trunc(sysdate,'mm') + 19)
          GROUP BY mfg.mfgorderid,u.attributevalue
        ) te
        GROUP BY te.mfgorderid,te.finishdate
    ) t
    LEFT JOIN enginepartplan ep ON ep.mfgorderid = t.mfgorderid
    LEFT JOIN Workcenter w ON w.workcenterid = ep.workcenterid
    WHERE ep.containerno IS NOT NULL AND ep.type = 1 AND w.workcentername IS NOT NULL
    GROUP BY t.mfgorderid,w.workcentername,t.finishdate
    --施工单所派工区
    UNION ALL
    SELECT t.mfgorderid,w.workcentername,t.finishdate
    FROM
    (
        SELECT te.mfgorderid,te.finishdate
        FROM
        (
          --未定义完工序，且最后一道大阶段工序在本月完成
          SELECT mfg.mfgorderid,u.attributevalue,MAX(ep.finishdate) finishdate
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE ep.type = 1 AND ep.productid = p.productid AND ep.completestepid IS NULL AND ws.islaststep = 1 AND ep.usecompleted = 1
                AND ep.finishdate >= (trunc(add_months(sysdate,-1),'mm') + 20) AND ep.finishdate <= (trunc(sysdate,'mm') + 19)
          GROUP BY mfg.mfgorderid,u.attributevalue
          --已定义完工序，且完工工序在本月完成
          UNION ALL
          SELECT mfg.mfgorderid,u.attributevalue,MAX(ep.finishdate) finishdate
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE ep.type = 1 AND ep.productid = p.productid AND ep.completestepid IS NOT NULL AND ep.workflowstepid = ep.completestepid AND ep.usecompleted = 1
                AND ep.finishdate >= (trunc(add_months(sysdate,-1),'mm') + 20) AND ep.finishdate <= (trunc(sysdate,'mm') + 19)
          GROUP BY mfg.mfgorderid,u.attributevalue
          --施工单，在本月内全部完工
          UNION ALL 
          SELECT mfg.mfgorderid,u.attributevalue,MAX(ep.finishdate) finishdate
          FROM enginepartplan ep
          LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
          LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
          LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
          LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
          LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
          WHERE ep.type = 2 AND ep.productid = p.productid
          HAVING SUM(ep.usecompleted) = COUNT(1) AND MAX(ep.finishdate) >= (trunc(add_months(sysdate,-1),'mm') + 20) AND MAX(ep.finishdate) <= (trunc(sysdate,'mm') + 19)
          GROUP BY mfg.mfgorderid,u.attributevalue
        ) te
        GROUP BY te.mfgorderid,te.finishdate
    ) t
    LEFT JOIN enginepartplan ep ON ep.mfgorderid = t.mfgorderid
    LEFT JOIN containerteamdispatch csd ON csd.containerid = nvl(ep.containerid,ep.enginecontainerid)
    LEFT JOIN team te ON te.teamid = csd.teamid
    LEFT JOIN shift s ON s.shiftid=te.shiftid
    LEFT JOIN Workcenter w ON w.workcenterid = s.workcenterid
    WHERE ep.type = 2 AND w.workcentername IS NOT NULL
    GROUP BY t.mfgorderid,w.workcentername,t.finishdate
) m
LEFT JOIN (
           SELECT ep.mfgorderid,COUNT(1) qty
           FROM enginepartplan ep
           WHERE ep.containerno IS NOT NULL AND ep.usecompleted = 0
           GROUP BY ep.mfgorderid
          ) n ON n.mfgorderid = m.mfgorderid
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = m.mfgorderid
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid = mfg.mfgorderid AND u2.userattributename = '订单类别' 
LEFT JOIN Userattribute u ON u.parentid = m.mfgorderid AND u.userattributename = '发动机'
WHERE 1=1
";
            if (para.ContainsKey("workcentername"))
            {
                sql += string.Format(" AND m.workcentername = '{0}' ", para["workcentername"].ToString());
            }
            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);
            return retR.DBTable;
        }

        //各中心未完成（剩余）明细 add zhunan 2019.12.12
        public DataTable GetWorkcenterNotFinishDetailInfo(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            string sql = @"
SELECT m.mfgorderid,m.workcentername,u.attributevalue,mfg.factorycompletiondate,NVL(n.qty,0) qty,'未完成' finishdate,
       CASE WHEN mfg.factorycompletiondate IS NULL THEN '计划完成时间为空,不能计算'
            WHEN TRUNC(SYSDATE - mfg.factorycompletiondate) > 0
                 THEN to_char(TRUNC(SYSDATE - mfg.factorycompletiondate))
            WHEN TRUNC(SYSDATE - mfg.factorycompletiondate) <= 0 THEN '0'
       END delayDays,
       CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
             ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                        WHEN ot.ordertypename = 'PD02' THEN '新机'
                        WHEN ot.ordertypename = 'PD03' THEN '新机'
                        WHEN ot.ordertypename = 'PD05' THEN '大修'
                        WHEN ot.ordertypename = 'PD06' THEN '检修'
                   ELSE ot.ordertypename END
                  ) 
       END ordertypename
FROM
(
   SELECT tep.mfgorderid,tep.workcentername
   FROM
   (
      --任务单所派工工区
      SELECT t.mfgorderid,w.workcentername
      FROM
      (
          SELECT te.mfgorderid
          FROM
          (
            --未定义完工序，且最后一道大阶段工序未完成
            SELECT mfg.mfgorderid,u.attributevalue
            FROM enginepartplan ep
            LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
            LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
            LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
            LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
            LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
            WHERE ep.type = 1 AND ep.productid = p.productid AND ep.completestepid IS NULL AND ws.islaststep = 1 AND ep.usecompleted = 0
            GROUP BY mfg.mfgorderid,u.attributevalue
            --已定义完工序，且完工序未完成
            UNION ALL
            SELECT mfg.mfgorderid,u.attributevalue
            FROM enginepartplan ep
            LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
            LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
            LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
            LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
            LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
            WHERE ep.type = 1 AND ep.productid = p.productid AND ep.completestepid IS NOT NULL AND ep.workflowstepid = ep.completestepid AND ep.usecompleted = 0
            GROUP BY mfg.mfgorderid,u.attributevalue
            --施工单，且未全部完成
            UNION ALL 
            SELECT mfg.mfgorderid,u.attributevalue
            FROM enginepartplan ep
            LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
            LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
            LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
            LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
            LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
            WHERE ep.type = 2 AND ep.productid = p.productid
            HAVING SUM(ep.usecompleted) <> COUNT(1)
            GROUP BY mfg.mfgorderid,u.attributevalue
          ) te
          GROUP BY te.mfgorderid
      ) t
      LEFT JOIN enginepartplan ep ON ep.mfgorderid = t.mfgorderid
      LEFT JOIN Workcenter w ON w.workcenterid = ep.workcenterid
      WHERE ep.containerno IS NOT NULL AND ep.type = 1 AND w.workcentername IS NOT NULL
      GROUP BY t.mfgorderid,w.workcentername
      --施工单所派工区
      UNION ALL
      SELECT t.mfgorderid,w.workcentername
      FROM
      (
          SELECT te.mfgorderid
          FROM
          (
            --未定义完工序，且最后一道大阶段未完成
            SELECT mfg.mfgorderid,u.attributevalue
            FROM enginepartplan ep
            LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
            LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
            LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
            LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
            LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
            WHERE ep.type = 1 AND ep.productid = p.productid AND ep.completestepid IS NULL AND ws.islaststep = 1 AND ep.usecompleted = 0
            GROUP BY mfg.mfgorderid,u.attributevalue
            --已定义完工序，且完工序未完成
            UNION ALL
            SELECT mfg.mfgorderid,u.attributevalue
            FROM enginepartplan ep
            LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
            LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
            LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
            LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
            LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
            WHERE ep.type = 1 AND ep.productid = p.productid AND ep.completestepid IS NOT NULL AND ep.workflowstepid = ep.completestepid AND ep.usecompleted = 0
            GROUP BY mfg.mfgorderid,u.attributevalue
            --施工单，且未全部完成
            UNION ALL 
            SELECT mfg.mfgorderid,u.attributevalue
            FROM enginepartplan ep
            LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
            LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
            LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
            LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
            LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
            WHERE ep.type = 2 AND ep.productid = p.productid
            HAVING SUM(ep.usecompleted) <> COUNT(1)
            GROUP BY mfg.mfgorderid,u.attributevalue
          ) te
          GROUP BY te.mfgorderid
      ) t
      LEFT JOIN enginepartplan ep ON ep.mfgorderid = t.mfgorderid
      LEFT JOIN containerteamdispatch csd ON csd.containerid = nvl(ep.containerid,ep.enginecontainerid)
      LEFT JOIN team te ON te.teamid = csd.teamid
      LEFT JOIN shift s ON s.shiftid=te.shiftid
      LEFT JOIN Workcenter w ON w.workcenterid = s.workcenterid
      WHERE ep.type = 2 AND w.workcentername IS NOT NULL
      GROUP BY t.mfgorderid,w.workcentername
  ) tep
  GROUP BY tep.mfgorderid,tep.workcentername
) m
LEFT JOIN (
           SELECT ep.mfgorderid,COUNT(1) qty
           FROM enginepartplan ep
           WHERE ep.containerno IS NOT NULL AND ep.usecompleted = 0
           GROUP BY ep.mfgorderid
          ) n ON n.mfgorderid = m.mfgorderid
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = m.mfgorderid
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid = mfg.mfgorderid AND u2.userattributename = '订单类别' 
LEFT JOIN Userattribute u ON u.parentid = m.mfgorderid AND u.userattributename = '发动机'
WHERE 1=1
";
            if (para.ContainsKey("workcentername"))
            {
                sql += string.Format(" AND m.workcentername = '{0}' ", para["workcentername"].ToString());
            }
            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);
            return retR.DBTable;
        }

        //各中心超期明细 add zhunan 2019.12.12
        public DataTable GetWorkcenterDelayDetailInfo(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            string sql = @"
SELECT m.mfgorderid,m.workcentername,u.attributevalue,mfg.factorycompletiondate,NVL(n.qty,0) qty,'未完成' finishdate,
       CASE WHEN mfg.factorycompletiondate IS NULL THEN '计划完成时间为空,不能计算'
            WHEN TRUNC(SYSDATE - mfg.factorycompletiondate) > 0
                 THEN to_char(TRUNC(SYSDATE - mfg.factorycompletiondate))
            WHEN TRUNC(SYSDATE - mfg.factorycompletiondate) <= 0 THEN '0'
       END delayDays,
       CASE WHEN u2.attributevalue IS NOT NULL THEN u2.attributevalue
             ELSE (CASE WHEN ot.ordertypename = 'PD01' THEN '批产'
                        WHEN ot.ordertypename = 'PD02' THEN '新机'
                        WHEN ot.ordertypename = 'PD03' THEN '新机'
                        WHEN ot.ordertypename = 'PD05' THEN '大修'
                        WHEN ot.ordertypename = 'PD06' THEN '检修'
                   ELSE ot.ordertypename END
                  ) 
       END ordertypename
FROM
(
    SELECT temp.mfgorderid,temp.workcentername
    FROM
    (
        --任务单所派工工区
        SELECT t.mfgorderid,w.workcentername
        FROM
        (
            SELECT te.mfgorderid
            FROM
            (
              --未定义完工序，且最后一道大阶段工序未完成
              SELECT mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate) delaydays
              FROM enginepartplan ep
              LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
              LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
              LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
              LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
              LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
              WHERE mfg.factorycompletiondate IS NOT NULL AND  ep.type = 1 AND ep.productid = p.productid 
                    AND ep.completestepid IS NULL AND ws.islaststep = 1 AND ep.usecompleted = 0
              GROUP BY mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate)
              --已定义完工序，且完工序未完成
              UNION ALL
              SELECT mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate) delaydays
              FROM enginepartplan ep
              LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
              LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
              LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
              LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
              LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
              WHERE mfg.factorycompletiondate IS NOT NULL AND ep.type = 1 AND ep.productid = p.productid 
                    AND ep.completestepid IS NOT NULL AND ep.workflowstepid = ep.completestepid AND ep.usecompleted = 0
              GROUP BY mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate)
              --施工单，且未全部完成
              UNION ALL 
              SELECT mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate) delaydays
              FROM enginepartplan ep
              LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
              LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
              LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
              LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
              LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
              WHERE mfg.factorycompletiondate IS NOT NULL AND ep.type = 2 AND ep.productid = p.productid
              HAVING SUM(ep.usecompleted) <> COUNT(1)
              GROUP BY mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate)
            ) te
            WHERE te.delaydays > 0
            GROUP BY te.mfgorderid
        ) t
        LEFT JOIN enginepartplan ep ON ep.mfgorderid = t.mfgorderid
        LEFT JOIN Workcenter w ON w.workcenterid = ep.workcenterid
        WHERE ep.containerno IS NOT NULL AND ep.type = 1 AND w.workcentername IS NOT NULL
        GROUP BY t.mfgorderid,w.workcentername
        --施工单所派工区
        UNION ALL
        SELECT t.mfgorderid,w.workcentername
        FROM
        (
            SELECT te.mfgorderid
            FROM
            (
              --未定义完工序，且最后一道大阶段未完成
              SELECT mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate) delaydays
              FROM enginepartplan ep
              LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
              LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
              LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
              LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
              LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
              WHERE mfg.factorycompletiondate IS NOT NULL AND ep.type = 1 AND ep.productid = p.productid 
                    AND ep.completestepid IS NULL AND ws.islaststep = 1 AND ep.usecompleted = 0
              GROUP BY mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate)
              --已定义完工序，且完工序未完成
              UNION ALL
              SELECT mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate) delaydays
              FROM enginepartplan ep
              LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
              LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
              LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
              LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
              LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
              WHERE mfg.factorycompletiondate IS NOT NULL AND ep.type = 1 AND ep.productid = p.productid 
                    AND ep.completestepid IS NOT NULL AND ep.workflowstepid = ep.completestepid AND ep.usecompleted = 0
              GROUP BY mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate)
              --施工单，且未全部完成
              UNION ALL 
              SELECT mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate) delaydays
              FROM enginepartplan ep
              LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
              LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename='发动机'
              LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
              LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid) 
              LEFT JOIN Workflowstep ws ON ws.workflowstepid = ep.workflowstepid
              WHERE mfg.factorycompletiondate IS NOT NULL AND ep.type = 2 AND ep.productid = p.productid
              HAVING SUM(ep.usecompleted) <> COUNT(1)
              GROUP BY mfg.mfgorderid,u.attributevalue,ROUND(SYSDATE-mfg.factorycompletiondate)
            ) te
            WHERE te.delaydays > 0
            GROUP BY te.mfgorderid
        ) t
        LEFT JOIN enginepartplan ep ON ep.mfgorderid = t.mfgorderid
        LEFT JOIN containerteamdispatch csd ON csd.containerid = nvl(ep.containerid,ep.enginecontainerid)
        LEFT JOIN team te ON te.teamid = csd.teamid
        LEFT JOIN Workcenter w ON w.workcenterid = te.workcenterid
        WHERE ep.type = 2 AND w.workcentername IS NOT NULL
        GROUP BY t.mfgorderid,w.workcentername
    ) temp
    GROUP BY temp.mfgorderid,temp.workcentername
) m
LEFT JOIN (
           SELECT ep.mfgorderid,COUNT(1) qty
           FROM enginepartplan ep
           WHERE ep.containerno IS NOT NULL AND ep.usecompleted = 0
           GROUP BY ep.mfgorderid
          ) n ON n.mfgorderid = m.mfgorderid
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = m.mfgorderid
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid = mfg.mfgorderid AND u2.userattributename = '订单类别' 
LEFT JOIN Userattribute u ON u.parentid = m.mfgorderid AND u.userattributename = '发动机'
WHERE 1=1
";
            if (para.ContainsKey("workcentername"))
            {
                sql += string.Format(" AND m.workcentername = '{0}' ", para["workcentername"].ToString());
            }
            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);
            return retR.DBTable;
        }

        //各中心、处室--未达标管理任务明细 add zhunan 2020.4.21
        public DataTable GetWorkcenterWdbDetailInfo(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            string sql = @"
SELECT  m.operationcontent,e.fullname,mt.completedate,m.localtime,
        DECODE(m.sqcdptype,0,'S指标',1,'Q指标',2,'C指标',3,'D指标',4,'P指标') operationtype,
        CASE WHEN m.cycle = 0 THEN '每天' || m.localtime
             WHEN m.cycle = 1 THEN '每天' || m.timenum || '次'
             WHEN m.cycle = 2 THEN '每周' || m.timenum || '次'
             WHEN m.cycle = 3 THEN '每月' || m.timenum || '次'
             WHEN m.cycle = 4 THEN '每月' || m.timenum || '日' END cycle,
        CASE WHEN mt.status = 1 THEN '1次' ELSE '0次' END dealcount,
        DECODE(mt.status,1,'已达标','未达标') status,
        mt.completedate resultdate
FROM manageroperationflowcomplete mt
LEFT JOIN manageroperationflow m ON m.manageroperationflowid = mt.manageroperationflowid
LEFT JOIN sessionvalues sv ON sv.employeeid = m.createemployeeid
LEFT JOIN Workcenter w ON w.workcenterid = sv.workcenterid
LEFT JOIN employee e ON e.employeeid = m.createemployeeid
WHERE trunc(mt.completedate) >= TRUNC(SYSDATE,'yyyy') AND trunc(mt.completedate) <= add_months(trunc(sysdate, 'yyyy'), 12) - 1
      AND mt.status <> 1 
";
            //工区
            if (para.ContainsKey("workcentername"))
            {
                sql += string.Format(" AND w.workcentername = '{0}' ", para["workcentername"].ToString());
            }
            sql += " ORDER BY mt.createdate";

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            return retR.DBTable;
        }

        //各工区考核（超期生产任务、未达标管理任务、考核得分） add zhunan 2020.4.21
        public DataTable GetWorkcenterKaoHe(Dictionary<string, object> para)
        {
            string sql = " SELECT w.workcentername FROM Workcenter w WHERE w.wctype = 1 ";

            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format("  AND w.factoryid = '{0}' ", para["factoryid"].ToString());
            }

            DataTable dtWorkcenter = dal.GetDataTable(sql);
            dtWorkcenter.Columns.Add("delayQty");//超期生产任务
            dtWorkcenter.Columns.Add("wdbQty");//未达标管理任务
            dtWorkcenter.Columns.Add("score");//考核得分

            //考核得分
            DataTable dtScore = ComputeWorkcenterScore(para);

            //超期生产任务
            DataTable dtDelay = GetWorkcenterDelayInfo(para);

            //未达标管理任务
            sql = @"
SELECT w.workcentername,count(m.operationcontent) wdbQty
FROM manageroperationflowcomplete mt
LEFT JOIN manageroperationflow m ON m.manageroperationflowid = mt.manageroperationflowid
LEFT JOIN sessionvalues sv ON sv.employeeid = m.createemployeeid
LEFT JOIN Workcenter w ON w.workcenterid = sv.workcenterid
WHERE trunc(mt.completedate) >= TRUNC(SYSDATE,'yyyy') AND trunc(mt.completedate) <= add_months(trunc(sysdate, 'yyyy'), 12) - 1
      AND mt.status <> 1 AND w.wctype = 1 
";
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format("  AND sv.factoryid = '{0}' ", para["factoryid"].ToString());
            }

            sql += " GROUP BY w.workcentername";

            DataTable dtWdb = dal.GetDataTable(sql);

            foreach (DataRow dr in dtWorkcenter.Rows)
            {
                string filter = string.Format(" workcentername = '{0}'", dr["workcentername"]);
                dr["delayQty"] = dtDelay.Select(filter).Length > 0 ? dtDelay.Select(filter)[0]["delay"] : 0;
                dr["wdbQty"] = dtWdb.Select(filter).Length > 0 ? dtWdb.Select(filter)[0]["wdbQty"] : 0;
                dr["score"] = dtScore.Select(filter).Length > 0 ? dtScore.Select(filter)[0]["score"] : 0;
            }

            return dtWorkcenter;
        }

        //各处室考核（超期问题、未达标管理任务、考核得分） add zhunan 2020.4.21
        public DataTable GetChuShiKaoHe(Dictionary<string, object> para)
        {
            string sql = string.Format("SELECT w.workcentername FROM workcenter w WHERE w.factoryid='{0}' AND w.wctype IS NULL ", para["factoryid"]);

            DataTable dtResult = dal.GetDataTable(sql);

            dtResult.Columns.Add("score");//考核得分
            dtResult.Columns.Add("chaoqi");//超期问题数
            dtResult.Columns.Add("wdbQty");//未达标管理任务数

            StringBuilder sb = new StringBuilder();

            //查询超期数
            //本月已关闭时间大于要求解决时间的问题 + 未关闭的问题且当前时间大于要求解决时间的问题
            sb = new StringBuilder();
            sb.AppendLine("SELECT m.workcentername,COUNT(1) chaoqi");
            sb.AppendLine("FROM");
            sb.AppendLine("(");
            sb.AppendLine("   SELECT rw.workcentername");
            sb.AppendLine("   FROM ufcproblem up");
            sb.AppendLine("   LEFT JOIN workcenter rw ON rw.workcenterid = up.workcenterid");
            sb.AppendLine("   WHERE up.ufcstatus <>  0 AND TRUNC(SYSDATE - up.ufcchulidate) >= 1");
            sb.AppendFormat("    AND up.factoryid='{0}'", para["factoryid"]);
            sb.AppendLine("   UNION ALL");
            sb.AppendLine("   SELECT rw.workcentername");
            sb.AppendLine("   FROM ufcproblem up");
            sb.AppendLine("   LEFT JOIN workcenter rw ON rw.workcenterid = up.workcenterid");
            sb.AppendLine("   WHERE up.ufcstatus = 0 AND up.ufcguanbidate IS NOT NULL AND up.ufcguanbidate >= trunc(sysdate,'month') AND up.ufcguanbidate <= trunc(last_day(sysdate)) + 1 ");
            sb.AppendLine("         AND TRUNC(up.ufcguanbidate - up.ufcchulidate) >= 1");
            sb.AppendFormat("    AND up.factoryid='{0}'", para["factoryid"]);
            sb.AppendLine(") m GROUP BY m.workcentername");

            DataTable dtChaoQi = dal.GetDataTable(sb.ToString());

            //查询各处室得分
            DataTable dtScore = ComputeChushiScore(para);

            //未达标管理任务
            sql = @"
SELECT w.workcentername,count(m.operationcontent) wdbQty
FROM manageroperationflowcomplete mt
LEFT JOIN manageroperationflow m ON m.manageroperationflowid = mt.manageroperationflowid
LEFT JOIN sessionvalues sv ON sv.employeeid = m.createemployeeid
LEFT JOIN Workcenter w ON w.workcenterid = sv.workcenterid
WHERE trunc(mt.completedate) >= TRUNC(SYSDATE,'yyyy') AND trunc(mt.completedate) <= add_months(trunc(sysdate, 'yyyy'), 12) - 1
      AND mt.status <> 1 AND w.wctype IS NULL 
";
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format("  AND sv.factoryid = '{0}' ", para["factoryid"].ToString());
            }

            sql += " GROUP BY w.workcentername";

            DataTable dtWdb = dal.GetDataTable(sql);

            foreach (DataRow dr in dtResult.Rows)
            {
                string filter = string.Format("workcentername = '{0}'", dr["workcentername"].ToString());
                dr["chaoqi"] = dtChaoQi.Select(filter).Length > 0 ? dtChaoQi.Select(filter)[0]["chaoqi"] : 0;
                dr["score"] = dtScore.Select(filter).Length > 0 ? dtScore.Select(filter)[0]["score"] : 0;
                dr["wdbQty"] = dtWdb.Select(filter).Length > 0 ? dtWdb.Select(filter)[0]["wdbQty"] : 0;
            }

            return dtResult;
        }

        //计算生产工区考核得分 add zhunan 2020.4.22
        public DataTable ComputeWorkcenterScore(Dictionary<string, object> para)
        {
            /*
             * 生产工区考核得分计算规则如下：
             * 
             * 各中心考核得分 = 任务得分 + S得分 + Q得分 + C得分 + D得分 + P得分
             * 
             * 任务得分：(1-超期任务数/(已完成任务数+剩余任务数)) * 0.4 * 100
             * 
             * S得分：(1-本年度S指标未达标管理任务数/本年度S指标管理任务数) * 0.15 * 100
             * 
             * Q得分：(1-本年度Q指标未达标管理任务数/本年度Q指标管理任务数) * 0.15 * 100
             * 
             * C得分：(1-本年度C指标未达标管理任务数/本年度C指标管理任务数) * 0.1 * 100
             * 
             * D得分：(1-本年度D指标未达标管理任务数/本年度D指标管理任务数) * 0.1 * 100
             * 
             * P得分：(1-本年度P指标未达标管理任务数/本年度P指标管理任务数) * 0.1 * 100
             * 
             */

            string sql = " SELECT w.workcentername FROM Workcenter w WHERE w.wctype = 1 ";

            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format("  AND w.factoryid = '{0}' ", para["factoryid"].ToString());
            }

            DataTable dtResult = dal.GetDataTable(sql);
            dtResult.Columns.Add("score");

            //工区生产任务
            DataTable dtTask = GetWorkcenterBarInfo(para);

            //查询生产工区SQCDP
            sql = @"
SELECT w.workcentername,m.sqcdptype type,COUNT(w.workcentername) total,SUM(CASE WHEN mt.status = 0 THEN 1 ELSE 0 END) wdb,
       SUM(CASE WHEN mt.status = 1 THEN 1 ELSE 0 END) ydb
FROM manageroperationflowcomplete mt
LEFT JOIN manageroperationflow m ON m.manageroperationflowid = mt.manageroperationflowid
LEFT JOIN sessionvalues sv ON sv.employeeid = m.createemployeeid
LEFT JOIN Workcenter w ON w.workcenterid = sv.workcenterid
WHERE trunc(mt.completedate) >= TRUNC(SYSDATE,'yyyy') AND trunc(mt.completedate) <= add_months(trunc(sysdate, 'yyyy'), 12) - 1
      AND m.type IN ('0','1','2','3','4') AND w.wctype = '1'
";
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND sv.factoryid = '{0}' ",para["factoryid"].ToString());
            }

            sql += "GROUP BY w.workcentername,m.sqcdptype";

            //工区SQCDP任务明细
            DataTable dtManageroperation = dal.GetDataTable(sql);

            foreach (DataRow dr in dtResult.Rows)
            {
                string filter = string.Format(" workcentername = '{0}'",dr["workcentername"].ToString());

                //生产任务得分计算
                double taskDelayQty = Convert.ToDouble(dtTask.Select(filter)[0]["delayQty"]);
                //生产任务数：已完成任务数+剩余任务数
                double taskTotalQty = Convert.ToDouble(dtTask.Select(filter)[0]["finishQty"]) + Convert.ToDouble(dtTask.Select(filter)[0]["notFinishQty"]);
                double taskScore = 40;
                if (taskTotalQty > 0)
                {
                    //生产任务得分：(1-超期任务数/生产任务数) * 0.4 * 100
                    taskScore = (1 - taskDelayQty / taskTotalQty) * 100 * 0.4;
                    taskScore = double.Parse(taskScore.ToString("0.00"));
                }

                //S得分计算
                string sFilter = string.Format(" workcentername = '{0}' and type = '0'",dr["workcentername"].ToString());
                double sScore = 15;
                if (dtManageroperation.Select(sFilter).Length > 0)
                {
                    double sFZ = Convert.ToDouble(dtManageroperation.Select(sFilter)[0]["wdb"]);
                    double sFM = Convert.ToDouble(dtManageroperation.Select(sFilter)[0]["total"]);
                    //S得分：(1-本年度S指标未达标管理任务数/本年度S指标管理任务数) * 0.15 * 100
                    sScore = (1 - sFZ / sFM) * 100 * 0.15;
                    sScore = double.Parse(sScore.ToString("0.00"));
                }

                //Q得分计算
                string qFilter = string.Format(" workcentername = '{0}' and type = '1'", dr["workcentername"].ToString());
                double qScore = 15;
                if (dtManageroperation.Select(qFilter).Length > 0)
                {
                    double qFZ = Convert.ToDouble(dtManageroperation.Select(qFilter)[0]["wdb"]);
                    double qFM = Convert.ToDouble(dtManageroperation.Select(qFilter)[0]["total"]);
                    qScore = (1 - qFZ / qFM) * 100 * 0.15;
                    qScore = double.Parse(qScore.ToString("0.00"));
                }

                //C得分
                string cFilter = string.Format(" workcentername = '{0}' and type = '2'", dr["workcentername"].ToString());
                double cScore = 10;
                if (dtManageroperation.Select(cFilter).Length > 0)
                {
                    double cFZ = Convert.ToDouble(dtManageroperation.Select(cFilter)[0]["wdb"]);
                    double cFM = Convert.ToDouble(dtManageroperation.Select(cFilter)[0]["total"]);
                    cScore = (1 - cFZ / cFM) * 100 * 0.1;
                    cScore = double.Parse(cScore.ToString("0.00"));
                }

                //D得分
                string dFilter = string.Format(" workcentername = '{0}' and type = '3'", dr["workcentername"].ToString());
                double dScore = 10;
                if (dtManageroperation.Select(dFilter).Length > 0)
                {
                    double dFZ = Convert.ToDouble(dtManageroperation.Select(dFilter)[0]["wdb"]);
                    double dFM = Convert.ToDouble(dtManageroperation.Select(dFilter)[0]["total"]);
                    dScore = (1 - dFZ / dFM) * 100 * 0.1;
                    dScore = double.Parse(dScore.ToString("0.00"));
                }

                //P得分
                string pFilter = string.Format(" workcentername = '{0}' and type = '4'", dr["workcentername"].ToString());
                double pScore = 10;
                if (dtManageroperation.Select(pFilter).Length > 0)
                {
                    double pFZ = Convert.ToDouble(dtManageroperation.Select(pFilter)[0]["wdb"]);
                    double pFM = Convert.ToDouble(dtManageroperation.Select(pFilter)[0]["total"]);
                    pScore = (1 - pFZ / pFM) * 100 * 0.1;
                    pScore = double.Parse(pScore.ToString("0.00"));
                }

                dr["score"] = taskScore + sScore + qScore + cScore + dScore + pScore;
            }

            return dtResult;
        }

        //查询生产工区考核得分明细 add zhunan 2020.4.22
        public DataTable GetWorkcenterScoreDetails(Dictionary<string, object> para)
        {
            DataTable dtResult = new DataTable();
            dtResult.Columns.Add("type");//考核项类型
            dtResult.Columns.Add("typename");//考核项名称
            dtResult.Columns.Add("ratio");//系数
            dtResult.Columns.Add("expression");//计算公式
            dtResult.Columns.Add("qty");//超期（未达标）数
            dtResult.Columns.Add("total");//总数
            dtResult.Columns.Add("score");//得分

            DataRow dr1 = dtResult.NewRow();
            dr1["type"] = "";
            dr1["typename"] = "生产任务";
            dr1["ratio"] = "0.4";
            dr1["expression"] = "（1 - 超期数/总数）* 100 * 0.4";
            dr1["qty"] = "0";
            dr1["total"] = "0";
            dr1["score"] = "40";
            dtResult.Rows.Add(dr1);

            DataRow dr2 = dtResult.NewRow();
            dr2["type"] = "3";
            dr2["typename"] = "S（安全）";
            dr2["ratio"] = "0.15";
            dr2["expression"] = "（1 - 未达标数/总数）* 100 * 0.15";
            dr2["qty"] = "0";
            dr2["total"] = "0";
            dr2["score"] = "15";
            dtResult.Rows.Add(dr2);

            DataRow dr3 = dtResult.NewRow();
            dr3["type"] = "4";
            dr3["typename"] = "Q（质量）";
            dr3["ratio"] = "0.15";
            dr3["expression"] = "（1 - 未达标数/总数）* 100 * 0.15";
            dr3["qty"] = "0";
            dr3["total"] = "0";
            dr3["score"] = "15";
            dtResult.Rows.Add(dr3);

            DataRow dr4 = dtResult.NewRow();
            dr4["type"] = "5";
            dr4["typename"] = "C（成本）";
            dr4["ratio"] = "0.1";
            dr4["expression"] = "（1 - 未达标数/总数）* 100 * 0.1";
            dr4["qty"] = "0";
            dr4["total"] = "0";
            dr4["score"] = "10";
            dtResult.Rows.Add(dr4);

            DataRow dr5 = dtResult.NewRow();
            dr5["type"] = "6";
            dr5["typename"] = "D（交付）";
            dr5["ratio"] = "0.1";
            dr5["expression"] = "（1 - 未达标数/总数）* 100 * 0.1";
            dr5["qty"] = "0";
            dr5["total"] = "0";
            dr5["score"] = "10";
            dtResult.Rows.Add(dr5);

            DataRow dr6 = dtResult.NewRow();
            dr6["type"] = "7";
            dr6["typename"] = "P（人员）";
            dr6["ratio"] = "0.1";
            dr6["expression"] = "（1 - 未达标数/总数）* 100 * 0.1";
            dr6["qty"] = "0";
            dr6["total"] = "0";
            dr6["score"] = "10";
            dtResult.Rows.Add(dr6);

            //工区生产任务
            DataTable dtTask = GetWorkcenterBarInfo(para);

            //查询生产工区SQCDP
            string sql = @"
SELECT w.workcentername,m.type,COUNT(w.workcentername) total,SUM(CASE WHEN mt.status = 0 THEN 1 ELSE 0 END) wdb,
       SUM(CASE WHEN mt.status = 1 THEN 1 ELSE 0 END) ydb
FROM manageroperationflowcomplete mt
LEFT JOIN manageroperationflow m ON m.manageroperationflowid = mt.manageroperationflowid
LEFT JOIN sessionvalues sv ON sv.employeeid = m.createemployeeid
LEFT JOIN Workcenter w ON w.workcenterid = sv.workcenterid
WHERE trunc(mt.completedate) >= TRUNC(SYSDATE,'yyyy') AND trunc(mt.completedate) <= add_months(trunc(sysdate, 'yyyy'), 12) - 1
      AND m.type IN ('0','1','2','3','4') AND w.wctype = '1'
";
            if (para.ContainsKey("workcentername"))
            {
                sql += string.Format(" AND w.workcentername = '{0}' ", para["workcentername"].ToString());
            }

            sql += "GROUP BY w.workcentername,m.type";

            //工区SQCDP任务明细
            DataTable dtManageroperation = dal.GetDataTable(sql);

            foreach (DataRow dr in dtResult.Rows)
            {
                if (dr["type"].ToString() == "3")
                {
                    //S得分计算
                    string filter = string.Format(" workcentername = '{0}' and type = '0'", para["workcentername"].ToString());
                    double score = 15;
                    if (dtManageroperation.Select(filter).Length > 0)
                    {

                        double FZ = Convert.ToDouble(dtManageroperation.Select(filter)[0]["wdb"]);
                        double FM = Convert.ToDouble(dtManageroperation.Select(filter)[0]["total"]);
                        score = (1 - FZ / FM) * 100 * 0.15;
                        score = double.Parse(score.ToString("0.00"));
                        dr["qty"] = dtManageroperation.Select(filter)[0]["wdb"];
                        dr["total"] = dtManageroperation.Select(filter)[0]["total"];
                        dr["score"] = score;
                    }
                }
                else if (dr["type"].ToString() == "4")
                {
                    //Q得分计算
                    string filter = string.Format(" workcentername = '{0}' and type = '1'", para["workcentername"].ToString());
                    double score = 15;
                    if (dtManageroperation.Select(filter).Length > 0)
                    {

                        double FZ = Convert.ToDouble(dtManageroperation.Select(filter)[0]["wdb"]);
                        double FM = Convert.ToDouble(dtManageroperation.Select(filter)[0]["total"]);
                        score = (1 - FZ / FM) * 100 * 0.15;
                        score = double.Parse(score.ToString("0.00"));
                        dr["qty"] = dtManageroperation.Select(filter)[0]["wdb"];
                        dr["total"] = dtManageroperation.Select(filter)[0]["total"];
                        dr["score"] = score;
                    }
                }
                else if (dr["type"].ToString() == "5")
                {
                    //C得分计算
                    string filter = string.Format(" workcentername = '{0}' and type = '2'", para["workcentername"].ToString());
                    double score = 10;
                    if (dtManageroperation.Select(filter).Length > 0)
                    {

                        double FZ = Convert.ToDouble(dtManageroperation.Select(filter)[0]["wdb"]);
                        double FM = Convert.ToDouble(dtManageroperation.Select(filter)[0]["total"]);
                        score = (1 - FZ / FM) * 100 * 0.1;
                        score = double.Parse(score.ToString("0.00"));
                        dr["qty"] = dtManageroperation.Select(filter)[0]["wdb"];
                        dr["total"] = dtManageroperation.Select(filter)[0]["total"];
                        dr["score"] = score;
                    }
                }
                else if (dr["type"].ToString() == "6")
                {
                    //D得分计算
                    string filter = string.Format(" workcentername = '{0}' and type = '3'", para["workcentername"].ToString());
                    double score = 10;
                    if (dtManageroperation.Select(filter).Length > 0)
                    {

                        double FZ = Convert.ToDouble(dtManageroperation.Select(filter)[0]["wdb"]);
                        double FM = Convert.ToDouble(dtManageroperation.Select(filter)[0]["total"]);
                        score = (1 - FZ / FM) * 100 * 0.1;
                        score = double.Parse(score.ToString("0.00"));
                        dr["qty"] = dtManageroperation.Select(filter)[0]["wdb"];
                        dr["total"] = dtManageroperation.Select(filter)[0]["total"];
                        dr["score"] = score;
                    }
                }
                else if (dr["type"].ToString() == "7")
                {
                    //P得分计算
                    string filter = string.Format(" workcentername = '{0}' and type = '4'", para["workcentername"].ToString());
                    double score = 10;
                    if (dtManageroperation.Select(filter).Length > 0)
                    {

                        double FZ = Convert.ToDouble(dtManageroperation.Select(filter)[0]["wdb"]);
                        double FM = Convert.ToDouble(dtManageroperation.Select(filter)[0]["total"]);
                        score = (1 - FZ / FM) * 100 * 0.1;
                        score = double.Parse(score.ToString("0.00"));
                        dr["qty"] = dtManageroperation.Select(filter)[0]["wdb"];
                        dr["total"] = dtManageroperation.Select(filter)[0]["total"];
                        dr["score"] = score;
                    }
                }
                else
                {
                    //生产任务得分计算
                    string filter = string.Format(" workcentername = '{0}'", para["workcentername"].ToString());

                    double taskDelayQty = Convert.ToDouble(dtTask.Select(filter)[0]["delayQty"]);
                    double taskTotalQty = Convert.ToDouble(dtTask.Select(filter)[0]["finishQty"]) + Convert.ToDouble(dtTask.Select(filter)[0]["notFinishQty"]);

                    double taskScore = 40;
                    if (taskTotalQty > 0)
                    {
                        //生产任务得分：(1-超期任务数/生产任务数) * 0.4 * 100
                        taskScore = (1 - taskDelayQty / taskTotalQty) * 100 * 0.4;
                        taskScore = double.Parse(taskScore.ToString("0.00"));
                        dr["qty"] = taskDelayQty;
                        dr["total"] = taskTotalQty;
                        dr["score"] = taskScore;
                    }
                }
            }
            return dtResult;
        }

        //计算各处室考核得分 add zhunan 2020.4.22
        public DataTable ComputeChushiScore(Dictionary<string, object> para)
        {
            /*
             * 各处室考核得分计算规则如下：
             * 
             * 各处室考核得分 = 生产问题得分 + S得分 + Q得分 + C得分 + D得分 + P得分
             * 
             * 生产问题得分：(1-超期生产问题数/生产问题数) * 0.3 * 100
             * 
             * S得分：(1-本年度S指标未达标管理任务数/本年度S指标管理任务数) * 0.15 * 100
             * 
             * Q得分：(1-本年度Q指标未达标管理任务数/本年度Q指标管理任务数) * 0.15 * 100
             * 
             * C得分：(1-本年度C指标未达标管理任务数/本年度C指标管理任务数) * 0.1 * 100
             * 
             * D得分：(1-本年度D指标未达标管理任务数/本年度D指标管理任务数) * 0.2 * 100
             * 
             * P得分：(1-本年度P指标未达标管理任务数/本年度P指标管理任务数) * 0.1 * 100
             * 
             */

            string sql = " SELECT w.workcentername FROM Workcenter w WHERE w.wctype IS NULL ";

            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format("  AND w.factoryid = '{0}' ", para["factoryid"].ToString());
            }

            DataTable dtResult = dal.GetDataTable(sql);
            dtResult.Columns.Add("score");

            //问题上报得分
            DataTable dtTask = GetRespWorkcenterProblemData(para);

            //查询生产工区SQCDP
            sql = @"
SELECT w.workcentername,m.type,COUNT(w.workcentername) total,SUM(CASE WHEN mt.status = 0 THEN 1 ELSE 0 END) wdb,
       SUM(CASE WHEN mt.status = 1 THEN 1 ELSE 0 END) ydb
FROM manageroperationflowcomplete mt
LEFT JOIN manageroperationflow m ON m.manageroperationflowid = mt.manageroperationflowid
LEFT JOIN sessionvalues sv ON sv.employeeid = m.createemployeeid
LEFT JOIN Workcenter w ON w.workcenterid = sv.workcenterid
WHERE trunc(mt.completedate) >= TRUNC(SYSDATE,'yyyy') AND trunc(mt.completedate) <= add_months(trunc(sysdate, 'yyyy'), 12) - 1
      AND m.type IN ('0','1','2','3','4') AND w.wctype IS NULL 
";
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND sv.factoryid = '{0}' ", para["factoryid"].ToString());
            }

            sql += "GROUP BY w.workcentername,m.type";

            //工区SQCDP任务明细
            DataTable dtManageroperation = dal.GetDataTable(sql);

            foreach (DataRow dr in dtResult.Rows)
            {
                string filter = string.Format(" workcentername = '{0}'", dr["workcentername"].ToString());


                double taskDelayQty = Convert.ToDouble(dtTask.Select(filter)[0]["chaoqi"]);//超期问题数
                double taskTotalQty = Convert.ToDouble(dtTask.Select(filter)[0]["total"]);//问题总数
                double taskScore = 30;
                if (taskTotalQty > 0)
                {
                    taskScore = (1 - taskDelayQty / taskTotalQty) * 100 * 0.3;
                    taskScore = double.Parse(taskScore.ToString("0.00"));
                }

                //S得分计算
                string sFilter = string.Format(" workcentername = '{0}' and type = '0'", dr["workcentername"].ToString());
                double sScore = 15;
                if (dtManageroperation.Select(sFilter).Length > 0)
                {
                    double sFZ = Convert.ToDouble(dtManageroperation.Select(sFilter)[0]["wdb"]);
                    double sFM = Convert.ToDouble(dtManageroperation.Select(sFilter)[0]["total"]);
                    sScore = (1 - sFZ / sFM) * 100 * 0.15;
                    sScore = double.Parse(sScore.ToString("0.00"));
                }

                //Q得分计算
                string qFilter = string.Format(" workcentername = '{0}' and type = '1'", dr["workcentername"].ToString());
                double qScore = 15;
                if (dtManageroperation.Select(qFilter).Length > 0)
                {
                    double qFZ = Convert.ToDouble(dtManageroperation.Select(qFilter)[0]["wdb"]);
                    double qFM = Convert.ToDouble(dtManageroperation.Select(qFilter)[0]["total"]);
                    qScore = (1 - qFZ / qFM) * 100 * 0.15;
                    qScore = double.Parse(qScore.ToString("0.00"));
                }

                //C得分
                string cFilter = string.Format(" workcentername = '{0}' and type = '2'", dr["workcentername"].ToString());
                double cScore = 10;
                if (dtManageroperation.Select(cFilter).Length > 0)
                {
                    double cFZ = Convert.ToDouble(dtManageroperation.Select(cFilter)[0]["wdb"]);
                    double cFM = Convert.ToDouble(dtManageroperation.Select(cFilter)[0]["total"]);
                    cScore = (1 - cFZ / cFM) * 100 * 0.1;
                    cScore = double.Parse(cScore.ToString("0.00"));
                }

                //D得分
                string dFilter = string.Format(" workcentername = '{0}' and type = '3'", dr["workcentername"].ToString());
                double dScore = 20;
                if (dtManageroperation.Select(dFilter).Length > 0)
                {
                    double dFZ = Convert.ToDouble(dtManageroperation.Select(dFilter)[0]["wdb"]);
                    double dFM = Convert.ToDouble(dtManageroperation.Select(dFilter)[0]["total"]);
                    dScore = (1 - dFZ / dFM) * 100 * 0.2;
                    dScore = double.Parse(dScore.ToString("0.00"));
                }

                //P得分
                string pFilter = string.Format(" workcentername = '{0}' and type = '4'", dr["workcentername"].ToString());
                double pScore = 10;
                if (dtManageroperation.Select(pFilter).Length > 0)
                {
                    double pFZ = Convert.ToDouble(dtManageroperation.Select(pFilter)[0]["wdb"]);
                    double pFM = Convert.ToDouble(dtManageroperation.Select(pFilter)[0]["total"]);
                    pScore = (1 - pFZ / pFM) * 100 * 0.1;
                    pScore = double.Parse(pScore.ToString("0.00"));
                }

                dr["score"] = taskScore + sScore + qScore + cScore + dScore + pScore;
            }

            return dtResult;
        }

        //查询各处室考核得分明细 add zhunan 2020.4.22
        public DataTable GetChushiScoreDetails(Dictionary<string, object> para)
        {
            DataTable dtResult = new DataTable();
            dtResult.Columns.Add("type");//考核项类型
            dtResult.Columns.Add("typename");//考核项名称
            dtResult.Columns.Add("ratio");//系数
            dtResult.Columns.Add("expression");//计算公式
            dtResult.Columns.Add("qty");//超期（未达标）数
            dtResult.Columns.Add("total");//总数
            dtResult.Columns.Add("score");//得分

            DataRow dr1 = dtResult.NewRow();
            dr1["type"] = "";
            dr1["typename"] = "问题上报";
            dr1["ratio"] = "0.3";
            dr1["expression"] = "（1 - 超期数/总数）* 100 * 0.3";
            dr1["qty"] = "0";
            dr1["total"] = "0";
            dr1["score"] = "30";
            dtResult.Rows.Add(dr1);

            DataRow dr2 = dtResult.NewRow();
            dr2["type"] = "0";
            dr2["typename"] = "S（安全）";
            dr2["ratio"] = "0.15";
            dr2["expression"] = "（1 - 未达标数/总数）* 100 * 0.15";
            dr2["qty"] = "0";
            dr2["total"] = "0";
            dr2["score"] = "15";
            dtResult.Rows.Add(dr2);

            DataRow dr3 = dtResult.NewRow();
            dr3["type"] = "1";
            dr3["typename"] = "Q（质量）";
            dr3["ratio"] = "0.15";
            dr3["expression"] = "（1 - 未达标数/总数）* 100 * 0.15";
            dr3["qty"] = "0";
            dr3["total"] = "0";
            dr3["score"] = "15";
            dtResult.Rows.Add(dr3);

            DataRow dr4 = dtResult.NewRow();
            dr4["type"] = "2";
            dr4["typename"] = "C（成本）";
            dr4["ratio"] = "0.1";
            dr4["expression"] = "（1 - 未达标数/总数）* 100 * 0.1";
            dr4["qty"] = "0";
            dr4["total"] = "0";
            dr4["score"] = "10";
            dtResult.Rows.Add(dr4);

            DataRow dr5 = dtResult.NewRow();
            dr5["type"] = "3";
            dr5["typename"] = "D（交付）";
            dr5["ratio"] = "0.2";
            dr5["expression"] = "（1 - 未达标数/总数）* 100 * 0.2";
            dr5["qty"] = "0";
            dr5["total"] = "0";
            dr5["score"] = "20";
            dtResult.Rows.Add(dr5);

            DataRow dr6 = dtResult.NewRow();
            dr6["type"] = "4";
            dr6["typename"] = "P（人员）";
            dr6["ratio"] = "0.1";
            dr6["expression"] = "（1 - 未达标数/总数）* 100 * 0.1";
            dr6["qty"] = "0";
            dr6["total"] = "0";
            dr6["score"] = "10";
            dtResult.Rows.Add(dr6);

            //处室问题上报
            DataTable dtTask = GetRespWorkcenterProblemData(para);

            //查询生产工区SQCDP
            string sql = @"
SELECT w.workcentername,m.type,COUNT(w.workcentername) total,SUM(CASE WHEN mt.status <> 1 THEN 1 ELSE 0 END) wdb,
       SUM(CASE WHEN mt.status = 1 THEN 1 ELSE 0 END) ydb
FROM manageroperationflowcomplete mt
LEFT JOIN manageroperationflow m ON m.manageroperationflowid = mt.manageroperationflowid
LEFT JOIN sessionvalues sv ON sv.employeeid = m.createemployeeid
LEFT JOIN Workcenter w ON w.workcenterid = sv.workcenterid
WHERE trunc(mt.completedate) >= TRUNC(SYSDATE,'yyyy') AND trunc(mt.completedate) <= add_months(trunc(sysdate, 'yyyy'), 12) - 1
      AND m.type IN ('0','1','2','3','4') AND w.wctype IS NULL 
";
            if (para.ContainsKey("workcentername"))
            {
                sql += string.Format(" AND w.workcentername = '{0}' ", para["workcentername"].ToString());
            }

            sql += "GROUP BY w.workcentername,m.type";

            //工区SQCDP任务明细
            DataTable dtManageroperation = dal.GetDataTable(sql);

            foreach (DataRow dr in dtResult.Rows)
            {
                if (dr["type"].ToString() == "3")
                {
                    //S得分计算
                    string filter = string.Format(" workcentername = '{0}' and type = '0'", para["workcentername"].ToString());
                    double score = 15;
                    if (dtManageroperation.Select(filter).Length > 0)
                    {

                        double FZ = Convert.ToDouble(dtManageroperation.Select(filter)[0]["wdb"]);
                        double FM = Convert.ToDouble(dtManageroperation.Select(filter)[0]["total"]);
                        score = (1 - FZ / FM) * 100 * 0.15;
                        score = double.Parse(score.ToString("0.00"));
                        dr["qty"] = dtManageroperation.Select(filter)[0]["wdb"];
                        dr["total"] = dtManageroperation.Select(filter)[0]["total"];
                        dr["score"] = score;
                    }
                }
                else if (dr["type"].ToString() == "4")
                {
                    //Q得分计算
                    string filter = string.Format(" workcentername = '{0}' and type = '1'", para["workcentername"].ToString());
                    double score = 15;
                    if (dtManageroperation.Select(filter).Length > 0)
                    {

                        double FZ = Convert.ToDouble(dtManageroperation.Select(filter)[0]["wdb"]);
                        double FM = Convert.ToDouble(dtManageroperation.Select(filter)[0]["total"]);
                        score = (1 - FZ / FM) * 100 * 0.15;
                        score = double.Parse(score.ToString("0.00"));
                        dr["qty"] = dtManageroperation.Select(filter)[0]["wdb"];
                        dr["total"] = dtManageroperation.Select(filter)[0]["total"];
                        dr["score"] = score;
                    }
                }
                else if (dr["type"].ToString() == "5")
                {
                    //C得分计算
                    string filter = string.Format(" workcentername = '{0}' and type = '2'", para["workcentername"].ToString());
                    double score = 10;
                    if (dtManageroperation.Select(filter).Length > 0)
                    {

                        double FZ = Convert.ToDouble(dtManageroperation.Select(filter)[0]["wdb"]);
                        double FM = Convert.ToDouble(dtManageroperation.Select(filter)[0]["total"]);
                        score = (1 - FZ / FM) * 100 * 0.1;
                        score = double.Parse(score.ToString("0.00"));
                        dr["qty"] = dtManageroperation.Select(filter)[0]["wdb"];
                        dr["total"] = dtManageroperation.Select(filter)[0]["total"];
                        dr["score"] = score;
                    }
                }
                else if (dr["type"].ToString() == "6")
                {
                    //D得分计算
                    string filter = string.Format(" workcentername = '{0}' and type = '3'", para["workcentername"].ToString());
                    double score = 20;
                    if (dtManageroperation.Select(filter).Length > 0)
                    {

                        double FZ = Convert.ToDouble(dtManageroperation.Select(filter)[0]["wdb"]);
                        double FM = Convert.ToDouble(dtManageroperation.Select(filter)[0]["total"]);
                        score = (1 - FZ / FM) * 100 * 0.2;
                        score = double.Parse(score.ToString("0.00"));
                        dr["qty"] = dtManageroperation.Select(filter)[0]["wdb"];
                        dr["total"] = dtManageroperation.Select(filter)[0]["total"];
                        dr["score"] = score;
                    }
                }
                else if (dr["type"].ToString() == "7")
                {
                    //P得分计算
                    string filter = string.Format(" workcentername = '{0}' and type = '4'", para["workcentername"].ToString());
                    double score = 10;
                    if (dtManageroperation.Select(filter).Length > 0)
                    {

                        double FZ = Convert.ToDouble(dtManageroperation.Select(filter)[0]["wdb"]);
                        double FM = Convert.ToDouble(dtManageroperation.Select(filter)[0]["total"]);
                        score = (1 - FZ / FM) * 100 * 0.1;
                        score = double.Parse(score.ToString("0.00"));
                        dr["qty"] = dtManageroperation.Select(filter)[0]["wdb"];
                        dr["total"] = dtManageroperation.Select(filter)[0]["total"];
                        dr["score"] = score;
                    }
                }
                else
                {
                    //问题上报计算
                    string filter = string.Format(" workcentername = '{0}'", para["workcentername"].ToString());

                    double taskDelayQty = Convert.ToDouble(dtTask.Select(filter)[0]["chaoqi"]);
                    double taskTotalQty = Convert.ToDouble(dtTask.Select(filter)[0]["total"]);

                    double taskScore = 30;
                    if (taskTotalQty > 0)
                    {
                        taskScore = (1 - taskDelayQty / taskTotalQty) * 100 * 0.3;
                        taskScore = double.Parse(taskScore.ToString("0.00"));
                        dr["qty"] = taskDelayQty;
                        dr["total"] = taskTotalQty;
                        dr["score"] = taskScore;
                    }
                }
            }
            return dtResult;
        }

        //查询责任单位本月问题上报 add zhunan 2020.4.22
        public DataTable GetRespWorkcenterProblemData(Dictionary<string, object> para)
        {
            string sql = string.Format("SELECT w.workcentername FROM workcenter w WHERE w.factoryid='{0}'", para["factoryid"]);

            DataTable dtResult = dal.GetDataTable(sql);

            dtResult.Columns.Add("total");//问题总数
            dtResult.Columns.Add("huifu");//回复数
            dtResult.Columns.Add("chaoqi");//超期数

            //查询问题总数
            //问题未关闭或本月关闭
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT m.workcentername,COUNT(1) total");
            sb.AppendLine("FROM");
            sb.AppendLine("(");
            sb.AppendLine("     SELECT rw.workcentername FROM ufcproblem up");
            sb.AppendLine("     LEFT JOIN workcenter rw ON rw.workcenterid = up.workcenterid");
            sb.AppendFormat("   WHERE up.ufcguanbidate IS NULL AND up.ufcstatus <> 0 AND up.factoryid='{0}'", para["factoryid"]);//未关闭

            sb.AppendLine("     UNION ALL");

            sb.AppendLine("     SELECT rw.workcentername FROM ufcproblem up");
            sb.AppendLine("     LEFT JOIN workcenter rw ON rw.workcenterid = up.workcenterid");
            sb.AppendFormat("   WHERE up.ufcstatus = 0 AND up.ufcguanbidate IS NOT NULL AND up.factoryid='{0}'", para["factoryid"]);
            sb.AppendLine("           AND up.ufcguanbidate >= trunc(sysdate,'month') AND up.ufcguanbidate <= trunc(last_day(sysdate)) + 1 ");
            sb.AppendLine(") m GROUP BY m.workcentername");

            DataTable dtTotal = dal.GetDataTable(sb.ToString());


            //查询回复数
            //本月已回复数
            sb = new StringBuilder();
            sb.AppendLine("SELECT rw.workcentername,COUNT(1) huifu");
            sb.AppendLine("FROM ufcproblem up");
            sb.AppendLine("LEFT JOIN workcenter rw ON rw.workcenterid = up.workcenterid");
            sb.AppendLine("WHERE up.ufcshenhedate IS NOT NULL AND up.ufcshenhedate >= trunc(sysdate,'month') AND up.ufcshenhedate <= trunc(last_day(sysdate)) + 1");
            sb.AppendFormat("    AND up.factoryid='{0}'", para["factoryid"]);
            sb.AppendLine("GROUP BY rw.workcentername");

            DataTable dtHuiFu = dal.GetDataTable(sb.ToString());

            //查询超期数
            //问题未关闭且当前已超期
            sb = new StringBuilder();
            sb.AppendLine("SELECT rw.workcentername,COUNT(1) chaoqi");
            sb.AppendLine("FROM ufcproblem up");
            sb.AppendLine("LEFT JOIN workcenter rw ON rw.workcenterid = up.workcenterid");
            sb.AppendLine("WHERE up.ufcstatus <>  0 AND TRUNC(SYSDATE - up.ufcchulidate) >= 1");
            sb.AppendFormat(" AND up.factoryid='{0}' ", para["factoryid"]);
            sb.AppendLine("GROUP BY rw.workcentername");

            DataTable dtChaoQi = dal.GetDataTable(sb.ToString());

            foreach (DataRow dr in dtResult.Rows)
            {
                string filter = string.Format("workcentername = '{0}'", dr["workcentername"].ToString());

                dr["total"] = dtTotal.Select(filter).Length > 0 ? dtTotal.Select(filter)[0]["total"] : 0;

                dr["huifu"] = dtHuiFu.Select(filter).Length > 0 ? dtHuiFu.Select(filter)[0]["huifu"] : 0;

                dr["chaoqi"] = dtChaoQi.Select(filter).Length > 0 ? dtChaoQi.Select(filter)[0]["chaoqi"] : 0;

            }

            return dtResult;
        }

        //各处室考核--超期问题明细
        public DataTable GetChuShiDelayProblem(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            string sql = @"
SELECT up.description,w.workcentername,e.fullname,up.ufcchulidate,ce.fullname createperson,up.ufccreatedate,up.ufcresult,
        DECODE(up.ufcwtdj,'3','一般（3天）',2,'急（1天）',1,'紧急（4H）',0,'特急（1H）') priority,
        CASE WHEN up.ufcstatus=0 THEN '已完成' WHEN up.ufcstatus=1 THEN '未处理' WHEN up.ufcstatus=2 THEN '处理中' WHEN up.ufcstatus=3 THEN '处理完' END AS ufcstatus,
        ROUND((up.ufcguanbidate-up.ufcchulidate),2) AS delayday
FROM ufcproblem up
LEFT JOIN Workcenter w ON w.workcenterid = up.workcenterid
LEFT JOIN employee e ON e.employeeid = up.ufctoemployeeid
LEFT JOIN employee ce ON ce.employeeid = up.ufccreateemployeeid
LEFT JOIN sessionvalues sv ON sv.employeeid = ce.employeeid
LEFT JOIN workcenter cw ON cw.workcenterid = sv.workcenterid
WHERE up.ufcguanbidate IS NOT NULL AND up.ufcguanbidate >= trunc(sysdate,'month') AND up.ufcguanbidate <= trunc(last_day(sysdate)) + 1   
      AND up.ufcguanbidate > up.ufcchulidate                        
";
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format("  AND up.factoryid = '{0}' ", para["factoryid"]);
            }
            if (para.ContainsKey("createWorkcenter"))
            {
                if (para["createWorkcenter"].ToString() != "All")
                {
                    sql += string.Format("  AND cw.workcentername LIKE '%{0}%' ", para["createWorkcenter"].ToString());
                }
            }
            if (para.ContainsKey("resWorkcenter"))
            {
                if (para["resWorkcenter"].ToString() != "All")
                {
                    sql += string.Format("  AND w.workcentername LIKE '%{0}%' ", para["resWorkcenter"].ToString());
                }
            }

            sql += @"
UNION ALL 
SELECT up.description,w.workcentername,e.fullname,up.ufcchulidate,ce.fullname createperson,up.ufccreatedate,up.ufcresult,
        DECODE(up.ufcwtdj,'3','一般（3天）',2,'急（1天）',1,'紧急（4H）',0,'特急（1H）') priority,
        CASE WHEN up.ufcstatus=0 THEN '已完成' WHEN up.ufcstatus=1 THEN '未处理' WHEN up.ufcstatus=2 THEN '处理中' WHEN up.ufcstatus=3 THEN '处理完' END AS ufcstatus,
        ROUND((SYSDATE-up.ufcchulidate),2) AS delayday
FROM ufcproblem up
LEFT JOIN Workcenter w ON w.workcenterid = up.workcenterid
LEFT JOIN employee e ON e.employeeid = up.ufctoemployeeid
LEFT JOIN employee ce ON ce.employeeid = up.ufccreateemployeeid
LEFT JOIN sessionvalues sv ON sv.employeeid = ce.employeeid
LEFT JOIN workcenter cw ON cw.workcenterid = sv.workcenterid
WHERE up.ufcguanbidate IS NULL  AND up.ufcstatus <> 0 AND TRUNC(SYSDATE - up.ufcchulidate) >= 1
";

            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format("  AND up.factoryid = '{0}' ", para["factoryid"]);
            }
            if (para.ContainsKey("createWorkcenter"))
            {
                if (para["createWorkcenter"].ToString() != "All")
                {
                    sql += string.Format("  AND cw.workcentername LIKE '%{0}%' ", para["createWorkcenter"].ToString());
                }
            }
            if (para.ContainsKey("resWorkcenter"))
            {
                if (para["resWorkcenter"].ToString() != "All")
                {
                    sql += string.Format("  AND w.workcentername LIKE '%{0}%' ", para["resWorkcenter"].ToString());
                }
            }

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            foreach (DataRow dr in retR.DBTable.Rows)
            {
                if (Convert.ToDecimal(dr["delayday"]) < 0)
                {
                    dr["delayday"] = 0;
                }
            }

            return retR.DBTable;
        }
    }
}
